package com.sigmazero13.bgjeeves.db;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.sigmazero13.bgjeeves.R;

public class GamesDbOpenHelper extends SQLiteOpenHelper {
	private static final String TAG = "GamesDbOpenHelper";
	private Context context;
	
	GamesDbOpenHelper(Context context) {
		super(context, context.getResources().getString(R.string.db_file),
				null, Integer.parseInt(context.getResources().getString(R.string.db_version)));
		
		this.context = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.w(TAG, "Creating new database with version " + Integer.parseInt(context.getResources().getString(R.string.db_version)));
		DbQueryRunner.createTable(db, DbTable.GAME_TABLE);
		DbQueryRunner.createTable(db, DbTable.PLAY_TABLE);
		DbQueryRunner.createTable(db, DbTable.PLAYER_TABLE);
		DbQueryRunner.createTable(db, DbTable.CUSTOM_INFO_TABLE);
		DbQueryRunner.createTable(db, DbTable.TEAM_TABLE);
		DbQueryRunner.createTable(db, DbTable.TEAM_PLAYER_TABLE);
		try {
			DbQueryRunner.runRawSqlStatements(db, loadSqlFile(R.raw.v5_sql_views_scenarios_and_players_with_teams));
			DbQueryRunner.runRawSqlStatements(db, loadSqlFile(R.raw.v5_sql_team_player_triggers));
			DbQueryRunner.runRawSqlStatements(db, loadSqlFile(R.raw.v6_sql_views_correct_players_with_teams));
		} catch (IOException e) {
			throw new RuntimeException(e);
		}
	}

	@Override
	public void onOpen(SQLiteDatabase db) {
		Log.w(TAG, "Opening database...");
		super.onOpen(db);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
				+ newVersion);

		if (newVersion <= oldVersion)
			return;

		db.beginTransaction();
		boolean success = true;
		for (int i = oldVersion; i < newVersion; i++) {
			int nextVersion = i + 1;
			switch (nextVersion) {
			case 4:
				success = upgradeTo4(db);
				break;
			case 5:
				success = upgradeTo5(db);
				break;
			case 6:
				success = upgradeTo6(db);
				break;
			}

			if (!success) {
				break;
			}
		}
		if (success) {
			db.setTransactionSuccessful();
		}
		db.endTransaction();
	}

	private boolean upgradeTo4(SQLiteDatabase db) {
		try {
			DbQueryRunner.addColumnToTable(db, DbTable.PLAY_TABLE, DbField.PLAY_LOCATION);
			DbQueryRunner.addColumnToTable(db, DbTable.PLAY_TABLE, DbField.PLAY_SCENARIO);
			DbQueryRunner.createTable(db, DbTable.CUSTOM_INFO_TABLE, 4);
		} catch (SQLException se) {
			Log.e(TAG, "Error adding columns for Upgrade 4...\n" + se);
			return false;
		}

		return true;
	}

	private boolean upgradeTo5(SQLiteDatabase db) {
		db.beginTransaction();
		try {
			// Move Teams to a separate table with a join
			DbQueryRunner.createTable(db, DbTable.TEAM_TABLE, 5);
			DbQueryRunner.createTable(db, DbTable.TEAM_PLAYER_TABLE, 5);
			DbQueryRunner.runRawSqlStatements(db, loadSqlFile(R.raw.v5_sql_populate_team_and_team_player_join));
			DbQueryRunner.dropTableColumnsForVersion(db, DbTable.PLAYER_TABLE, 5);
	
			DbQueryRunner.addColumnToTable(db, DbTable.PLAY_TABLE, DbField.PLAY_COMMENTS);
	
			// NEW VIEWS
			DbQueryRunner.runRawSqlStatements(db, loadSqlFile(R.raw.v5_sql_views_scenarios_and_players_with_teams));
			
			// NEW TRIGGERS
			DbQueryRunner.runRawSqlStatements(db, loadSqlFile(R.raw.v5_sql_team_player_triggers));
		} catch (SQLException sqle) {
			Log.e(TAG, "Error upgrading to version 5", sqle);
			db.endTransaction();
			return false;
		} catch (IOException ioe) {
			Log.e(TAG, "Error loading SQL file for upgrade to version 5", ioe);
			db.endTransaction();
			return false;
		}
		
		db.setTransactionSuccessful();
		db.endTransaction();
		return true;
	}
	
	private boolean upgradeTo6(SQLiteDatabase db) {
		db.beginTransaction();
		try {
			// CORRECT VIEWS
			DbQueryRunner.runRawSqlStatements(db, loadSqlFile(R.raw.v6_sql_views_correct_players_with_teams));
		} catch (SQLException sqle) {
			Log.e(TAG, "Error upgrading to version 6", sqle);
			db.endTransaction();
			return false;
		} catch (IOException ioe) {
			Log.e(TAG, "Error loading SQL file for upgrade to version 6", ioe);
			db.endTransaction();
			return false;
		}
		
		db.setTransactionSuccessful();
		db.endTransaction();
		return true;
	}
	
	
	private List<String> loadSqlFile(int resourceId) throws IOException {
		InputStream is = context.getResources().openRawResource(resourceId);
		InputStreamReader isr = new InputStreamReader(is);
		BufferedReader rd = new BufferedReader(isr);
		
		List<String> sqlLines = new ArrayList<String>();
		while (true) {
			String line = rd.readLine();
			if (line == null) break;
			sqlLines.add(line);
		}
		return sqlLines;
	}
}